﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Dynamic;
using System.Linq;
using ContractManager.Common.Class;
using ContractManager.Entity;
using Speed.Data;

namespace ContractManager.BLL.Business
{
    public static class ClsDynamic
    {
        public static void InsertObject(string tableName, string keyField, dynamic data, dynamic dataDetail, List<FieldTables> list, int formNo,string fieldNameAutoNumber)
        {
            var lstViewFields=new List<string>();
            var db = new PetaPoco.Database("ContractContext");
            db.BeginTransaction();
            if (!String.IsNullOrEmpty(fieldNameAutoNumber))
            {
                string sql = String.Format("SELECT MAX({0}) FROM {1}", fieldNameAutoNumber, tableName);
                string lastId = db.ExecuteScalar<string>(sql);
                sql = String.Format("SELECT dbo.taGetPMNextObjectNumber({0},N'{1}')", formNo, lastId);
                string nextNumber = db.ExecuteScalar<string>(sql);
                ((IDictionary<String, Object>)data)[fieldNameAutoNumber] = nextNumber;
                
            }
            db.Insert(tableName, keyField, data);
            //Update Auto Number
            
            foreach (FieldTables fieldTablese in list)
            {
                var dataTable = (DataTable)(((IDictionary<String, Object>)dataDetail)[fieldTablese.FieldTableName]);
                var field = ClsFieldTables.SelectByFormAndFieldName(fieldTablese.FieldTableName, formNo);
                if (field != null)
                {
                    string[] arrViewFields = field.FieldTableListViewFieldReadOnly.Split(',');
                    lstViewFields.AddRange(arrViewFields);
                    DataTable dtAdd = dataTable.GetChanges(DataRowState.Added);
                    DataTable dtUpdate = dataTable.GetChanges(DataRowState.Modified);
                    DataTable dtDelete = dataTable.GetChanges(DataRowState.Deleted);

                    #region "Them chi tiet"
                    if (dtAdd != null && dtAdd.Rows.Count > 0)
                    {
                        dtAdd.BeginLoadData();
                        foreach (DataRow row in dtAdd.Rows)
                        {
                            dynamic ret = new ExpandoObject();
                            foreach (DataColumn column in dtAdd.Columns.Cast<DataColumn>().Where(column => !lstViewFields.Contains(column.ColumnName)))
                            {
                                if (column.ColumnName.Equals(keyField))
                                {
                                    var value = ((IDictionary<String, Object>)data)[keyField];
                                    ((IDictionary<String, Object>)ret).Add(column.ColumnName, value);
                                }
                                else
                                {
                                    ((IDictionary<String, Object>)ret).Add(column.ColumnName, row[column.ColumnName]);
                                }
                            }
                            db.Insert(fieldTablese.GridTable, fieldTablese.GridTableKeyField, ret);
                        }
                        dtAdd.EndLoadData();
                    }
                    #endregion

                    #region "Update thong tin chi tiet"
                    if (dtUpdate != null && dtUpdate.Rows.Count > 0)
                    {
                        dtUpdate.BeginLoadData();
                        foreach (DataRow row in dtUpdate.Rows)
                        {
                            dynamic ret = new ExpandoObject();
                            foreach (DataColumn column in dtUpdate.Columns.Cast<DataColumn>().Where(column => !lstViewFields.Contains(column.ColumnName)))
                            {
                                ((IDictionary<String, Object>)ret).Add(column.ColumnName, row[column.ColumnName]);
                            }
                            db.Update(fieldTablese.GridTable, fieldTablese.GridTableKeyField, ret);
                        }
                        dtUpdate.EndLoadData();
                    }
                    #endregion

                    #region "Xoá thông tin chi tiết"
                    if (dtDelete != null && dtDelete.Rows.Count > 0)
                    {
                        dtDelete.BeginLoadData();
                        foreach (DataRow row in dtDelete.Rows)
                        {
                            dynamic ret = new ExpandoObject();
                            FieldTables tablese = fieldTablese;
                            foreach (DataColumn column in 
                                                          from DataColumn column in dtDelete.Columns 
                                                          where !lstViewFields.Contains(column.ColumnName) 
                                      where column.ColumnName.Equals(tablese.GridTableKeyField) 
                                      select column)
                            {
                                ((IDictionary<String, Object>)ret).Add(fieldTablese.GridTableKeyField,
                                    row[column.ColumnName, DataRowVersion.Original]);
                            }
                            db.Delete(fieldTablese.GridTable, fieldTablese.GridTableKeyField, ret);
                        }
                        dtDelete.EndLoadData();
                    }
                    #endregion
                }
                
            }
            db.CompleteTransaction();
        }

        public static void UpdateObject(string tableName, string keyField, dynamic data, dynamic dataDetail, List<FieldTables> list, int formNo)
        {
            var lstViewFields = new List<string>();
            var db = new PetaPoco.Database("ContractContext");
            db.BeginTransaction();
            db.Update(tableName, keyField, data);
            foreach (FieldTables fieldTablese in list)
            {
                var dataTable = (DataTable)(((IDictionary<String, Object>)dataDetail)[fieldTablese.FieldTableName]);
                var field = ClsFieldTables.SelectByFormAndFieldName(fieldTablese.FieldTableName, formNo);
                if (field != null)
                {
                    string[] arrViewFields = field.FieldTableListViewFieldReadOnly.Split(',');
                    lstViewFields.AddRange(arrViewFields);

                    DataTable dtAdd = dataTable.GetChanges(DataRowState.Added);
                    DataTable dtUpdate = dataTable.GetChanges(DataRowState.Modified);
                    DataTable dtDelete = dataTable.GetChanges(DataRowState.Deleted);

                    if (dtAdd != null && dtAdd.Rows.Count > 0)
                    {
                        dtAdd.BeginLoadData();
                        foreach (DataRow row in dtAdd.Rows)
                        {
                            dynamic ret = new ExpandoObject();
                            foreach (DataColumn column in dtAdd.Columns.Cast<DataColumn>().Where(column => !lstViewFields.Contains(column.ColumnName)))
                            {
                                if (column.ColumnName.Equals(keyField))
                                {
                                    var value = ((IDictionary<String, Object>)data)[keyField];
                                    ((IDictionary<String, Object>)ret).Add(column.ColumnName, value);
                                }
                                else
                                {
                                    ((IDictionary<String, Object>)ret).Add(column.ColumnName, row[column.ColumnName]);
                                }
                            }
                            db.Insert(fieldTablese.GridTable, fieldTablese.GridTableKeyField, ret);
                        }
                        dtAdd.EndLoadData();
                    }

                    if (dtUpdate != null && dtUpdate.Rows.Count > 0)
                    {
                        dtUpdate.BeginLoadData();
                        foreach (DataRow row in dtUpdate.Rows)
                        {
                            dynamic ret = new ExpandoObject();
                            foreach (DataColumn column in dtUpdate.Columns.Cast<DataColumn>().Where(column => !lstViewFields.Contains(column.ColumnName)))
                            {
                                ((IDictionary<String, Object>)ret).Add(column.ColumnName, row[column.ColumnName]);
                            }
                            db.Update(fieldTablese.GridTable, fieldTablese.GridTableKeyField, ret);
                        }
                        dtUpdate.EndLoadData();
                    }

                    if (dtDelete != null && dtDelete.Rows.Count > 0)
                    {
                        dtDelete.BeginLoadData();
                        foreach (DataRow row in dtDelete.Rows)
                        {
                            dynamic ret = new ExpandoObject();
                            FieldTables tablese = fieldTablese;
                            foreach (DataColumn column in 
                                                            from DataColumn column in dtDelete.Columns 
                                                            where !lstViewFields.Contains(column.ColumnName) 
                                      where column.ColumnName.Equals(tablese.GridTableKeyField) select column)
                            {
                                ((IDictionary<String, Object>)ret).Add(fieldTablese.GridTableKeyField,
                                    row[column.ColumnName, DataRowVersion.Original]);
                            }
                            db.Delete(fieldTablese.GridTable, fieldTablese.GridTableKeyField, ret);
                        }
                        dtDelete.EndLoadData();
                    }
                }
                
            }

            db.CompleteTransaction();
        }

        public static void DeleteObject(string tableName, string keyField, dynamic data,int formNo)
        {
            var masterId = ((IDictionary<String, Object>)data)[keyField];
            string sqlMaster = String.Format("DELETE FROM {0} WHERE {1}={2}", tableName, keyField, masterId);
            using (Database db = Sys.NewDb())
            {
                db.BeginTransaction();
                db.ExecuteNonQuery(sqlMaster);
                var list = ClsFieldTables.SelectByForm(formNo);
                var listDetail = list.Where(op => op.FieldTalbeType == 6);

                foreach (var detail in listDetail)
                {
                    string tName = detail.GridTable;
                    string sql = String.Format("DELETE FROM {0} WHERE {1}={2}", tName, keyField, masterId);
                    db.ExecuteNonQuery(sql);
                }
                db.Commit();
            }
        }

        public static int GetCountObject(string sql)
        {
            using (var db = Sys.NewDb())
            {

                return db.ExecuteInt32(sql);
            }
        }


        public static DataTable GetObject(string sql)
        {
            using (var db = Sys.NewDb())
            {
                
                return db.ExecuteDataTable(sql);
            }
        }

        public static DataTable GetObject(string procedureName, int formNo, int voucherCate, DateTime fromDate, DateTime toDate)
        {
            using (Database db = Sys.NewDb())
            {
                string commandText = String.Format("EXEC dbo.{0} {1},{2},{3},{4},'{5}','{6}'",
                                                    procedureName,
                                                   ClsVariable.AsSysMyCoYearId,
                                                   formNo,
                                                   voucherCate,
                                                   ClsVariable.EmployeeId,
                                                   fromDate.ToString("dd/MMM/yyyy"),
                                                   toDate.ToString("dd/MMM/yyyy"));
                return db.ExecuteDataTable(commandText);
            }

            
        }
    }
}